﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Data.Common;
using System.Data.OleDb;

namespace MetaDataRepository
{
    public class Repository
    {
        private SQLMetaDataEntities dbContext;

        internal class ConnectionStringProperties
        {
            internal const string DataSource = "Data Source";
            internal const string Server = "Server";
            internal const string Location = "Location";
            internal const string Provider = "Provider";
            internal const string Database = "Database";
            internal const string InitialCatalog = "Initial Catalog";
        }

        private List<string> databasePrefixExclusions;
        public List<string> DatabasePrefixExclusions
        {
            get
            {
                return databasePrefixExclusions;
            }
        }

        private bool databaseNameOnlyCompare = false;
        public bool DatabaseNameOnlyCompare
        {
            get
            {
                return databaseNameOnlyCompare;
            }
            set
            {
                databaseNameOnlyCompare = value;
            }
        }

        #region Constructors
        /// <summary>
        /// Generic Connection.  This will attempt to connect to .\SQL2008R2 with database unt0SQLMetaData.
        /// </summary>
        public Repository()
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

            // Set the properties for the data source.
            sqlBuilder.DataSource = @".\SQL2008R2";
            sqlBuilder.InitialCatalog = "unt0SQLMetaData";
            sqlBuilder.IntegratedSecurity = true;
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = "System.Data.SqlClient";
            entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
            entityBuilder.Metadata = @"res://*/SQLMetaDataModel.csdl|res://*/SQLMetaDataModel.ssdl|res://*/SQLMetaDataModel.msl";

            dbContext = new SQLMetaDataEntities(entityBuilder.ToString());
            databasePrefixExclusions = new List<string>();
        }

        /// <summary>
        /// Integration Security connection to the Repository.
        /// </summary>
        /// <param name="SQLServerName"></param>
        /// <param name="DatabaseName"></param>
        public Repository(String SQLServerName, String DatabaseName)
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

            // Set the properties for the data source.
            sqlBuilder.DataSource = SQLServerName;
            sqlBuilder.InitialCatalog = DatabaseName;
            sqlBuilder.IntegratedSecurity = true;
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = "System.Data.SqlClient";
            entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
            entityBuilder.Metadata = @"res://*/SQLMetaDataModel.csdl|res://*/SQLMetaDataModel.ssdl|res://*/SQLMetaDataModel.msl";

            dbContext = new SQLMetaDataEntities(entityBuilder.ToString());
            databasePrefixExclusions = new List<string>();
        }

        /// <summary>
        /// SQL Authentication connection to the Repository.
        /// </summary>
        /// <param name="SQLServerName"></param>
        /// <param name="DatabaseName"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        public Repository(String SQLServerName, String DatabaseName, String UserName, String Password)
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

            // Set the properties for the data source.
            sqlBuilder.DataSource = SQLServerName;
            sqlBuilder.InitialCatalog = DatabaseName;
            sqlBuilder.IntegratedSecurity = false;
            sqlBuilder.UserID = UserName;
            sqlBuilder.Password = Password;
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = "System.Data.SqlClient";
            entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
            entityBuilder.Metadata = @"res://*/SQLMetaDataModel.csdl|res://*/SQLMetaDataModel.ssdl|res://*/SQLMetaDataModel.msl";

            dbContext = new SQLMetaDataEntities(entityBuilder.ToString());
            databasePrefixExclusions = new List<string>();
        }
        #endregion

        #region Execution(s)
        /// <summary>
        /// Gets an Execution that matches the commandLine and executionDate, or creates a new record and saves it.
        /// </summary>
        /// <param name="commandLine"></param>
        /// <param name="executionDate"></param>
        /// <returns>A new or existing Execution</returns>
        public mdExecution GetExecution(String commandLine, DateTime executionDate)
        {
            mdExecution Execution = (from mdExecution exec in dbContext.mdExecutions where exec.CommandLine == commandLine & exec.ExecutionDate == executionDate select exec).FirstOrDefault();
            if (Execution == null)
            {
                Execution = dbContext.mdExecutions.Add(new mdExecution { CommandLine = commandLine, ExecutionDate = executionDate });
                dbContext.SaveChanges();
            }
            return Execution;
        }

        /// <summary>
        /// Gets a specific ExectionID
        /// </summary>
        /// <param name="executionID"></param>
        /// <returns></returns>
        public mdExecution GetExecution(Int32 executionID)
        {
            mdExecution Execution = dbContext.mdExecutions.Find(executionID);
            return Execution;
        }
        #endregion

        #region SSISPackage(s)
        /// <summary>
        /// Gets an SSIS Package by ExecutionID and Name.  Will return an existing or newly saved package.
        /// </summary>
        /// <param name="executionID"></param>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public mdSSISPackage GetSSISPackage(mdExecution execution, String packageName)
        {
            return GetSSISPackage(execution, packageName, null, null, null);
        }

        /// <summary>
        /// Gets an SSIS Package by the ID value.
        /// </summary>
        /// <param name="packageID"></param>
        /// <returns></returns>
        public mdSSISPackage GetSSISPackage(Int32 packageID)
        {
            return dbContext.mdSSISPackages.Find(packageID);
        }

        /// <summary>
        /// Gets an SSIS Package by ExecutionID and Name.  Will return an existing or newly saved package.
        /// </summary>
        /// <param name="executionID"></param>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public mdSSISPackage GetSSISPackage(mdExecution execution, String packageName, String description, String location, String PackageGUID)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            
            mdSSISPackage ssisPackage = (from mdSSISPackage ssisPack in dbContext.mdSSISPackages where ssisPack.mdExecutions_ExecutionID == execution.ExecutionID & ssisPack.PackageName == packageName select ssisPack).FirstOrDefault();
            if (ssisPackage == null)
            {
                ssisPackage = dbContext.mdSSISPackages.Add(new mdSSISPackage { mdExecutions_ExecutionID = execution.ExecutionID, PackageName = packageName, PackageDescription = description, PackageLocation = location, PackageGUID = PackageGUID });
                dbContext.SaveChanges();
            }
            return ssisPackage;
        }
        #endregion

        #region SSISContainer(s)
        /// <summary>
        /// Get (or create) an SSIS Container
        /// </summary>
        /// <param name="package">The mdSSISPackage that this container belongs to</param>
        /// <param name="name">The name of this container (unique for the SSIS package)</param>
        /// <param name="containerType">The type of the container</param>
        /// <returns>The referenced SSIS Container</returns>
        public mdSSISContainer GetContainer(mdSSISPackage package, String name, String containerType)
        {
            System.Diagnostics.Debug.Assert(package != null);
            mdSSISContainer container = (from mdSSISContainer cont in dbContext.mdSSISContainers where cont.ContainerName == name & cont.mdSSISPackages_SSISPackageID == package.SSISPackageID select cont).FirstOrDefault();
            if (container == null)
            {
                container = dbContext.mdSSISContainers.Add(new mdSSISContainer { mdSSISPackages_SSISPackageID = package.SSISPackageID, ContainerName = name, ContainerType = containerType });
                dbContext.SaveChanges();
            }
            return container;
        }
        #endregion

        #region Component(s)

        public mdComponent GetComponent(mdSSISPackage package, String name, String componentType, String description)
        {
            return GetComponent(package, name, componentType, description, null);
        }
        
        /// <summary>
        /// Get (or create) a component.
        /// </summary>
        /// <param name="package">The mdSSISPackage that this component belongs to</param>
        /// <param name="name">The name of this component (Unique for the SSIS Package (no container))</param>
        /// <param name="componentType">The type of the component</param>
        /// <returns>The referenced component</returns>
        public mdComponent GetComponent(mdSSISPackage package, String name, String componentType, String description, String query)
        {
            System.Diagnostics.Debug.Assert(package != null);
            mdComponent component = (from mdComponent comp in dbContext.mdComponents where comp.ComponentName == name & comp.mdSSISPackages_SSISPackageID == package.SSISPackageID & comp.mdSSISContainers_SSISContainerID == null select comp).FirstOrDefault();
            if (component == null)
            {
                component = dbContext.mdComponents.Add(new mdComponent { mdSSISPackages_SSISPackageID = package.SSISPackageID, ComponentName = name, ComponentType = componentType, ComponentComment = description, ComponentQuery = query });
                dbContext.SaveChanges();
            }
            return component;
        }


        public mdComponent GetComponent(mdSSISPackage package, String name, String componentType, mdSSISContainer container)
        {
            return GetComponent(package, name, componentType, container, null, null);
        }
        
        /// <summary>
        /// Get (or create) a component that is within a Container.
        /// </summary>
        /// <param name="package">The mdSSISPackage that this component belongs to</param>
        /// <param name="name">The name of this component (Unique for the SSIS Package/Container)</param>
        /// <param name="componentType">The type of the component</param>
        /// <param name="container">The mdSSISContainer container that this is within</param>
        /// <returns>The referenced component</returns>
        public mdComponent GetComponent(mdSSISPackage package, String name, String componentType, mdSSISContainer container, String description, String query)
        {
            System.Diagnostics.Debug.Assert(package != null);
            mdComponent component = (from mdComponent comp in dbContext.mdComponents where comp.ComponentName == name & comp.mdSSISPackages_SSISPackageID == package.SSISPackageID & comp.mdSSISContainers_SSISContainerID == container.SSISContainerID select comp).FirstOrDefault();
            if (component == null)
            {
                component = dbContext.mdComponents.Add(new mdComponent { mdSSISPackages_SSISPackageID = package.SSISPackageID, ComponentName = name, ComponentType = componentType, mdSSISContainers_SSISContainerID = container.SSISContainerID });
                dbContext.SaveChanges();
            }
            return component;
        }

        /// <summary>
        /// Gets a Mapping between two components
        /// </summary>
        /// <param name="fromComponent"></param>
        /// <param name="toComponent"></param>
        /// <returns></returns>
        public mdComponentToComponentMap GetComponentMap(mdComponent fromComponent, mdComponent toComponent)
        {
            System.Diagnostics.Debug.Assert(fromComponent != null);
            System.Diagnostics.Debug.Assert(toComponent != null);
            mdComponentToComponentMap componentMap = (from mdComponentToComponentMap map in dbContext.mdComponentToComponentMaps where map.mdComponents_SourceComponentID == fromComponent.ComponentID & map.mdComponents_TargetComponentID == toComponent.ComponentID select map).FirstOrDefault();
            if (componentMap == null)
            {
                componentMap = dbContext.mdComponentToComponentMaps.Add(new mdComponentToComponentMap { mdComponents_SourceComponentID = fromComponent.ComponentID, mdComponents_TargetComponentID = toComponent.ComponentID });
                dbContext.SaveChanges();
            }
            return componentMap;
        }

        /// <summary>
        /// Gets a Mapping between a Component and a File
        /// </summary>
        /// <param name="fromComponent"></param>
        /// <param name="toFile"></param>
        /// <returns></returns>
        public mdComponentToFileMap GetComponentMap(mdComponent fromComponent, mdFile toFile)
        {
            System.Diagnostics.Debug.Assert(fromComponent != null);
            System.Diagnostics.Debug.Assert(toFile != null);
            mdComponentToFileMap componentMap = (from mdComponentToFileMap map in dbContext.mdComponentToFileMaps where map.mdComponents_ComponentID == fromComponent.ComponentID & map.mdFiles_FileID == toFile.FileID select map).FirstOrDefault();
            if (componentMap == null)
            {
                componentMap = dbContext.mdComponentToFileMaps.Add(new mdComponentToFileMap { mdComponents_ComponentID = fromComponent.ComponentID, mdFiles_FileID = toFile.FileID });
                dbContext.SaveChanges();
            }
            return componentMap;
        }

        /// <summary>
        /// Gets a Mapping between a Component and an Object
        /// </summary>
        /// <param name="fromComponent"></param>
        /// <param name="toObject"></param>
        /// <returns></returns>
        public mdComponentToObjectMap GetComponentMap(mdComponent fromComponent, mdObject toObject)
        {
            System.Diagnostics.Debug.Assert(fromComponent != null);
            System.Diagnostics.Debug.Assert(toObject != null);
            mdComponentToObjectMap componentMap = (from mdComponentToObjectMap map in dbContext.mdComponentToObjectMaps where map.mdComponents_ComponentID == fromComponent.ComponentID & map.mdObjects_ObjectID == toObject.ObjectID select map).FirstOrDefault();
            if (componentMap == null)
            {
                componentMap = dbContext.mdComponentToObjectMaps.Add(new mdComponentToObjectMap { mdComponents_ComponentID = fromComponent.ComponentID, mdObjects_ObjectID = toObject.ObjectID , isComponentToObject = true});
                dbContext.SaveChanges();
            }
            return componentMap;
        }

        /// <summary>
        /// Gets a Mapping between an Object and a Component 
        /// </summary>
        /// <param name="fromComponent"></param>
        /// <param name="toObject"></param>
        /// <returns></returns>
        public mdComponentToObjectMap GetComponentMap(mdObject fromObject, mdComponent toComponent)
        {
            System.Diagnostics.Debug.Assert(toComponent != null);
            System.Diagnostics.Debug.Assert(fromObject != null);
            mdComponentToObjectMap componentMap = (from mdComponentToObjectMap map in dbContext.mdComponentToObjectMaps where map.mdComponents_ComponentID == toComponent.ComponentID & map.mdObjects_ObjectID == fromObject.ObjectID select map).FirstOrDefault();
            if (componentMap == null)
            {
                componentMap = dbContext.mdComponentToObjectMaps.Add(new mdComponentToObjectMap { mdComponents_ComponentID = toComponent.ComponentID, mdObjects_ObjectID = fromObject.ObjectID, isComponentToObject = false });
                dbContext.SaveChanges();
            }
            return componentMap;
        }

        public void UpdateComponentQuery(mdComponent targetComponent, String query)
        {
            targetComponent.ComponentQuery = query;
            dbContext.SaveChanges();
        }

        #endregion

        #region ComponentColumn(s)
        /// <summary>
        /// Get (or create) a column that belongs to a component
        /// </summary>
        /// <param name="component">The mdComponent that this column belongs to</param>
        /// <param name="columnName">The name of the column</param>
        /// <param name="dataType">the Data Type of the column.  Can be Null or String.Empty...</param>
        /// <param name="expression">the expression that created the data in this column...</param>
        /// <returns></returns>
        public mdComponentColumn GetComponentColumn(mdComponent component, String columnName, String dataType, String expression)
        {
            System.Diagnostics.Debug.Assert(component != null);
            mdComponentColumn column = (from mdComponentColumn col in dbContext.mdComponentColumns where col.mdComponents_ComponentID == component.ComponentID & col.ColumnName == columnName select col).FirstOrDefault();
            if (column == null)
            {
                column = dbContext.mdComponentColumns.Add(new mdComponentColumn { mdComponents_ComponentID = component.ComponentID, ColumnName = columnName, DataType = dataType, Expression = expression});
                dbContext.SaveChanges();
            }
            return column;
        }
        #endregion

        #region ComponentColumnMaps
        /// <summary>
        /// Get or Create a column to column mapping between Component Columns
        /// </summary>
        /// <param name="fromColumn"></param>
        /// <param name="toColumn"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public mdComponentColumnToComponentColumnMap GetComponentColumnMap(mdComponentColumn fromColumn, mdComponentColumn toColumn, String expression)
        {
            System.Diagnostics.Debug.Assert(fromColumn != null);
            System.Diagnostics.Debug.Assert(toColumn != null);
            mdComponentColumnToComponentColumnMap columnMap = (from mdComponentColumnToComponentColumnMap col in dbContext.mdComponentColumnToComponentColumnMaps where col.mdComponentColumns_SourceComponentColumnID == fromColumn.ComponentColumnID & col.mdComponentColumns_TargetComponentColumnID == toColumn.ComponentColumnID select col).FirstOrDefault();
            if (columnMap == null)
            {
                columnMap = dbContext.mdComponentColumnToComponentColumnMaps.Add(new mdComponentColumnToComponentColumnMap { mdComponentColumns_SourceComponentColumnID = fromColumn.ComponentColumnID, mdComponentColumns_TargetComponentColumnID = toColumn.ComponentColumnID, Expression = expression });
                dbContext.SaveChanges();
            }
            return columnMap;
        }
        #endregion


        #region File(s)
        /// <summary>
        /// Gets a File by Execute and Name.  Will return an existing or newly saved file.
        /// </summary>
        /// <param name="execution">mdExecution execution that has already been defined.</param>
        /// <param name="fileName">The name of the file.</param>
        /// <returns></returns>
        public mdFile GetFile(mdExecution execution, String fileName)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            mdFile file = (from mdFile mdfile in dbContext.mdFiles where mdfile.mdExecutions_ExecutionID == execution.ExecutionID & mdfile.FileName == fileName select mdfile).FirstOrDefault();
            if (file == null)
            {
                file = dbContext.mdFiles.Add(new mdFile { mdExecutions_ExecutionID = execution.ExecutionID, FileName = fileName });
                dbContext.SaveChanges();
            }
            return file;
        }

        public mdComponentToFileMap GetComponentFileMap (mdComponent fromComponent, mdFile toFile)
        {
            System.Diagnostics.Debug.Assert(fromComponent != null);
            System.Diagnostics.Debug.Assert(toFile != null);
            mdComponentToFileMap map = (from mdComponentToFileMap filemap in dbContext.mdComponentToFileMaps where filemap.mdComponents_ComponentID == fromComponent.ComponentID & filemap.mdFiles_FileID == toFile.FileID select filemap).FirstOrDefault();
            if (map == null)
            {
                map = dbContext.mdComponentToFileMaps.Add(new mdComponentToFileMap { mdComponents_ComponentID = fromComponent.ComponentID, mdFiles_FileID = toFile.FileID, isComponentToFile = true });
                dbContext.SaveChanges();
            }
            return map;
        }

        public mdComponentToFileMap GetComponentFileMap(mdFile fromFile, mdComponent toComponent)
        {
            System.Diagnostics.Debug.Assert(toComponent != null);
            System.Diagnostics.Debug.Assert(fromFile != null);
            mdComponentToFileMap map = (from mdComponentToFileMap filemap in dbContext.mdComponentToFileMaps where filemap.mdComponents_ComponentID == toComponent.ComponentID & filemap.mdFiles_FileID == fromFile.FileID select filemap).FirstOrDefault();
            if (map == null)
            {
                map = dbContext.mdComponentToFileMaps.Add(new mdComponentToFileMap { mdComponents_ComponentID = toComponent.ComponentID, mdFiles_FileID = fromFile.FileID, isComponentToFile = false });
                dbContext.SaveChanges();
            }
            return map;
        }


        #endregion

        #region FileColumn(s)
        /// <summary>
        /// Get (or create) a column that belongs to a file
        /// </summary>
        /// <param name="file">The mdFile that this column belongs to</param>
        /// <param name="columnName">The name of the column</param>
        /// <param name="dataType">the Data Type of the column.  Can be Null or String.Empty...</param>
        /// <returns></returns>
        public mdFileColumn GetFileColumn(mdFile file, String columnName, String dataType)
        {
            System.Diagnostics.Debug.Assert(file != null);
            if (columnName.Length > 128)
            {
                columnName = columnName.Substring(0, 128);
            }
            mdFileColumn column = (from mdFileColumn col in dbContext.mdFileColumns where col.mdFiles_FileID == file.FileID & col.ColumnName == columnName select col).FirstOrDefault();
            if (column == null)
            {
                if (String.IsNullOrEmpty(dataType))
                {
                    column = dbContext.mdFileColumns.Add(new mdFileColumn { mdFiles_FileID = file.FileID, ColumnName = columnName });
                }
                else
                {
                    column = dbContext.mdFileColumns.Add(new mdFileColumn { mdFiles_FileID = file.FileID, ColumnName = columnName, DataType = dataType });
                }
                dbContext.SaveChanges();
            }
            return column;
        }
        #endregion

        #region ReportServer(s)
        /// <summary>
        /// Gets a Report Server by Execution and URL.  Will return an existing or newly saved Report Server.
        /// </summary>
        /// <param name="execution">mdExecution execution that has already been defined.</param>
        /// <param name="serverURL">The URL of the Server</param>
        /// <returns></returns>
        public mdReportServer GetReportServer(mdExecution execution, String serverURL)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            mdReportServer reportServer = (from mdReportServer server in dbContext.mdReportServers where server.mdExecutions_ExecutionID == execution.ExecutionID & server.ReportServerURL == serverURL select server).FirstOrDefault();
            if (reportServer == null)
            {
                reportServer = dbContext.mdReportServers.Add(new mdReportServer { mdExecutions_ExecutionID = execution.ExecutionID, ReportServerURL = serverURL });
                dbContext.SaveChanges();
            }
            return reportServer;
        }
        #endregion

        #region Report(s)
        /// <summary>
        /// Gets a Report by ReportServer and URL.  Will return an existing or newly saved Report.
        /// </summary>
        /// <param name="reportServer">mdReportServer server that has already been defined.</param>
        /// <param name="reportURL">The URL of the Report</param>
        /// <returns></returns>
        public mdReport GetReport(mdReportServer reportServer, String reportURL)
        {
            System.Diagnostics.Debug.Assert(reportServer != null);
            mdReport report = (from mdReport mdreport in dbContext.mdReports where mdreport.mdReportServers_ReportServerID == reportServer.ReportServerID & mdreport.ReportURL == reportURL select mdreport).FirstOrDefault();
            if (report == null)
            {
                report = dbContext.mdReports.Add(new mdReport { mdReportServers_ReportServerID = reportServer.ReportServerID, ReportURL = reportURL });
                dbContext.SaveChanges();
            }
            return report;
        }

        /// <summary>
        /// Gets a mapping between a Report and an Object
        /// </summary>
        /// <param name="fromReport">The report that is using an object</param>
        /// <param name="toObject">The object that is being used</param>
        /// <returns></returns>
        public mdReportToObjectMap GetReportMap(mdReport fromReport, mdObject toObject)
        {
            System.Diagnostics.Debug.Assert(fromReport != null);
            System.Diagnostics.Debug.Assert(toObject != null);
            mdReportToObjectMap componentMap = (from mdReportToObjectMap map in dbContext.mdReportToObjectMaps where map.mdReports_ReportID == fromReport.ReportID & map.mdObjects_ObjectID == toObject.ObjectID select map).FirstOrDefault();
            if (componentMap == null)
            {
                componentMap = dbContext.mdReportToObjectMaps.Add(new mdReportToObjectMap { mdReports_ReportID = fromReport.ReportID, mdObjects_ObjectID = toObject.ObjectID });
                dbContext.SaveChanges();
            }
            return componentMap;
        }
        #endregion

        #region Database Server(s)
        /// <summary>
        /// Gets a database server entity.
        /// </summary>
        /// <param name="execution">The execution that this is contained within</param>
        /// <param name="serverName">The name of the server</param>
        /// <returns></returns>
        public mdDatabaseServer GetDatabaseServer(mdExecution execution, String serverName, String providerName)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            mdDatabaseServer dbServer = (from mdDatabaseServer server in dbContext.mdDatabaseServers where server.mdExecutions_ExecutionID == execution.ExecutionID & server.ServerName == serverName select server).FirstOrDefault();
            if (dbServer == null)
            {
                mdDatabaseType dbType = GetDatabaseServerType(providerName);
                dbServer = dbContext.mdDatabaseServers.Add(new mdDatabaseServer { mdExecutions_ExecutionID = execution.ExecutionID, ServerName = serverName, mdDatabaseTypes_DatabaseTypeID = dbType.DatabaseTypeID });
                dbContext.SaveChanges();
            }
            return dbServer;
        }

        private mdDatabaseType GetDatabaseServerType(string providerName)
        {
            mdDatabaseType databaseType = (from mdDatabaseType dbType in dbContext.mdDatabaseTypes where dbType.Name == providerName select dbType).FirstOrDefault();
            if (databaseType == null)
            {
                databaseType = dbContext.mdDatabaseTypes.Add(new mdDatabaseType { Name = providerName });
                dbContext.SaveChanges();
            }
            return databaseType;
        }
        #endregion

        #region Database(s)
        /// <summary>
        /// Gets a database from a connection string.
        /// Make sure that DatabaseNameOnlyCompare and/or DatabasePrefixExclusions have been set before calling this.
        /// </summary>
        /// <param name="execution">The current execution</param>
        /// <param name="connectionString">The connection string to parse for Server and Database.</param>
        /// <returns></returns>
        public mdDatabase GetDatabase(mdExecution execution, String connectionString)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            mdDatabase database = null;
            String serverName = GetServerNameFromConnectionString(connectionString);
            String databaseName = CleansedDBName(GetDatabaseNameFromConnectionString(connectionString));
            String providerName = GetProviderFromConnectionString(connectionString);
            
            if (!String.IsNullOrEmpty(serverName) && !String.IsNullOrEmpty(databaseName) && !String.IsNullOrEmpty(providerName))
            {
                mdDatabaseServer databaseServer = null;
                if (this.DatabaseNameOnlyCompare)
                {
                    // Try and find the database in ANY server for the current execution...
                    database = (from mdDatabase db in dbContext.mdDatabases where db.mdDatabaseServer.mdExecutions_ExecutionID == execution.ExecutionID & db.DatabaseName == databaseName select db).FirstOrDefault();
                    if (database == null)
                    {
                        // It wasn't found, so create the Server and Database Entries.
                        databaseServer = GetDatabaseServer(execution, serverName, providerName);
                        database = dbContext.mdDatabases.Add(new mdDatabase { mdDatabaseServer_DatabaseServerID = databaseServer.DatabaseServerID, DatabaseName = databaseName });
                        dbContext.SaveChanges();
                    }
                }
                else
                {
                    // Get the database Server, and then retrieve/add the database on that server.
                    databaseServer = GetDatabaseServer(execution, serverName, providerName);
                    database = (from mdDatabase db in dbContext.mdDatabases where db.mdDatabaseServer_DatabaseServerID == databaseServer.DatabaseServerID & db.DatabaseName == databaseName select db).FirstOrDefault();
                    if (database == null)
                    {
                        database = dbContext.mdDatabases.Add(new mdDatabase { mdDatabaseServer_DatabaseServerID = databaseServer.DatabaseServerID, DatabaseName = databaseName });
                        dbContext.SaveChanges();
                    }
                }
            }
            else
            {
                throw new Exception(String.Format("Connection String \"{0}\" was not Valid.\r\nUnable to get Server Name and/or Database Name and/or Provider Name.", connectionString));
            }
            return database;
        }

        /// <summary>
        /// Gets a database from a prepoulated databaseServer.  Used for SSAS access.
        /// </summary>
        /// <param name="execution">The current execution</param>
        /// <param name="databaseServer">The required mdDatabaseServer</param>
        /// <param name="databaseName">The name of the database</param>
        /// <returns></returns>
        public mdDatabase GetDatabase(mdExecution execution, mdDatabaseServer databaseServer, String databaseName)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            System.Diagnostics.Debug.Assert(databaseServer != null);
            mdDatabase database = null;
            databaseName = CleansedDBName(databaseName);
            if (this.DatabaseNameOnlyCompare)
            {
                // Try and find the database in ANY server for the current execution...
                database = (from mdDatabase db in dbContext.mdDatabases where db.mdDatabaseServer.mdExecutions_ExecutionID == execution.ExecutionID & db.DatabaseName == databaseName select db).FirstOrDefault();
                if (database == null)
                {
                    // It wasn't found, so create the Server and Database Entries.
                    database = dbContext.mdDatabases.Add(new mdDatabase { mdDatabaseServer_DatabaseServerID = databaseServer.DatabaseServerID, DatabaseName = databaseName });
                    dbContext.SaveChanges();
                }
            }
            else
            {
                database = (from mdDatabase db in dbContext.mdDatabases where db.mdDatabaseServer_DatabaseServerID == databaseServer.DatabaseServerID & db.DatabaseName == databaseName select db).FirstOrDefault();
                if (database == null)
                {
                    database = dbContext.mdDatabases.Add(new mdDatabase { mdDatabaseServer_DatabaseServerID = databaseServer.DatabaseServerID, DatabaseName = databaseName });
                    dbContext.SaveChanges();
                }
            }
            return database;
        }

        /// <summary>
        /// Removes the first database prefix found, or the original name.
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private String CleansedDBName (String dbName)
        {
            if (String.IsNullOrEmpty(dbName))
                return dbName;
            foreach (String prefix in databasePrefixExclusions)
            {
                if (dbName.StartsWith(prefix))
                {
                    return dbName.Substring(prefix.Length);
                }
            }
            return dbName;
        }

        #endregion

        #region Object(s)
        /// <summary>
        /// Get the object type.  Expected to contain Table, Procedure, Function, DSView, etc.
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private mdObjectType GetObjectType(String name)
        {
            mdObjectType objectType = (from mdObjectType ot in dbContext.mdObjectTypes where ot.Name == name select ot).FirstOrDefault();
            if (objectType == null)
            {
                objectType = dbContext.mdObjectTypes.Add(new mdObjectType { Name = name });
                dbContext.SaveChanges();
            }
            return objectType;
        }

        /// <summary>
        /// Gets an object of the specific type from the specific database.
        /// </summary>
        /// <param name="database"></param>
        /// <param name="schema"></param>
        /// <param name="name"></param>
        /// <param name="typeName"></param>
        /// <returns></returns>
        public mdObject GetObject(mdDatabase database, String schema, String name, String typeName)
        {
            return GetObject(database, schema, name, typeName, null);
        }

        /// <summary>
        /// Gets an object of the specific type from the specific database.
        /// </summary>
        /// <param name="database"></param>
        /// <param name="schema"></param>
        /// <param name="name"></param>
        /// <param name="typeName"></param>
        /// <param name="description">The optional description of the object being saved.</param>
        /// <returns></returns>
        public mdObject GetObject(mdDatabase database, String schema, String name, String typeName, String description)
        {
            System.Diagnostics.Debug.Assert(database != null);
            mdObjectType objectType = GetObjectType(typeName);
            mdObject targetObject = (from mdObject obj in dbContext.mdObjects where obj.mdDatabases_DatabaseID == database.DatabaseID & obj.ObjectSchema == schema & obj.ObjectName == name select obj).FirstOrDefault();  // & obj.mdObjectTypes_ObjectTypeID == objectType.ObjectTypeID
            if (targetObject == null)
            {
                targetObject = dbContext.mdObjects.Add(new mdObject { ObjectSchema = schema, ObjectName = name, mdObjectTypes_ObjectTypeID = objectType.ObjectTypeID, mdDatabases_DatabaseID = database.DatabaseID, ObjectComment = description });
                dbContext.SaveChanges();
            }
            else
            {
                // This is a special case where if the typeName = View, then we change the stored type.  This is done because SSIS thinks all Views are Tables (and so does SSRS).
                if (targetObject.mdObjectTypes_ObjectTypeID != objectType.ObjectTypeID && (typeName == "View" || targetObject.mdObjectType.Name == "UnresolvedEntity"))
                {
                    targetObject.mdObjectTypes_ObjectTypeID = objectType.ObjectTypeID;
                    dbContext.SaveChanges();
                }
            }
            return targetObject;
        }

        public void UpdateObjectsComment(mdObject objectToUpdate, String description)
        {
            objectToUpdate.ObjectComment = description;
            dbContext.SaveChanges();
        }

        public void UpdateObjectsQuery(mdObject objectToUpdate, String query)
        {
            objectToUpdate.ObjectQuery = query;
            dbContext.SaveChanges();
        }

        /// <summary>
        /// Gets a Mapping between two objects
        /// </summary>
        /// <param name="fromObject"></param>
        /// <param name="toObject"></param>
        /// <returns></returns>
        public mdObjectToObjectMap GetObjectMap(mdObject fromObject, mdObject toObject, Boolean fromIsContainer)
        {
            System.Diagnostics.Debug.Assert(fromObject != null);
            System.Diagnostics.Debug.Assert(toObject != null);
            mdObjectToObjectMap objectMap = (from mdObjectToObjectMap map in dbContext.mdObjectToObjectMaps where map.mdObjects_SourceObjectID == fromObject.ObjectID & map.mdObjects_TargetObjectID == toObject.ObjectID select map).FirstOrDefault();
            if (objectMap == null)
            {
                objectMap = dbContext.mdObjectToObjectMaps.Add(new mdObjectToObjectMap { mdObjects_SourceObjectID = fromObject.ObjectID, mdObjects_TargetObjectID = toObject.ObjectID, SourceIsContainer = fromIsContainer });
                dbContext.SaveChanges();
            }
            return objectMap;
        }

        #endregion

        #region ObjectColumn(s)
        /// <summary>
        /// Get (or create) a column that belongs to a object
        /// </summary>
        /// <param name="fromObject">The mdObject that this column belongs to</param>
        /// <param name="columnName">The name of the column</param>
        /// <param name="dataType">the Data Type of the column.  Can be Null or String.Empty...</param>
        /// <returns></returns>
        public mdObjectColumn GetComponentColumn(mdObject fromObject, String columnName, String dataType)
        {
            System.Diagnostics.Debug.Assert(fromObject != null);
            if (columnName.Length > 128)
            {
                columnName = columnName.Substring(0, 128);
            }
            mdObjectColumn column = (from mdObjectColumn col in dbContext.mdObjectColumns where col.mdObjects_ObjectID == fromObject.ObjectID & col.ColumnName == columnName select col).FirstOrDefault();
            if (column == null)
            {
                if (String.IsNullOrEmpty(dataType))
                {
                    column = dbContext.mdObjectColumns.Add(new mdObjectColumn { mdObjects_ObjectID = fromObject.ObjectID, ColumnName = columnName });
                }
                else
                {
                    column = dbContext.mdObjectColumns.Add(new mdObjectColumn { mdObjects_ObjectID = fromObject.ObjectID, ColumnName = columnName, DataType = dataType });
                }
                dbContext.SaveChanges();
            }
            return column;
        }
        #endregion

        #region Connection String Functions
        /// <summary>
        /// Return the Server Name from a Connection String
        /// </summary>
        /// <param name="connectionString">the connection string to get the server name out of</param>
        /// <returns>null if it's not a vaild connection string...</returns>
        private String GetServerNameFromConnectionString(String connectionString)
        {
            DbConnectionStringBuilder builtString = GetConnectionStringBuilder(connectionString);
            if (builtString == null)
            {
                return null;
            }
            return GetServerName(builtString);
        }

        /// <summary>
        /// Return the Database Name from a Connectino String
        /// </summary>
        /// <param name="connectionString">the connection string to get the database name out of</param>
        /// <returns>null if it's not a vaild connection string...</returns>
        private string GetDatabaseNameFromConnectionString(String connectionString)
        {
            DbConnectionStringBuilder builtString = GetConnectionStringBuilder(connectionString);
            if (builtString == null)
            {
                return null;
            }
            return GetDatabaseName(builtString);
        }

        private string GetProviderFromConnectionString(string connectionString)
        {
            DbConnectionStringBuilder builtString = GetConnectionStringBuilder(connectionString);
            if (builtString == null)
            {
                return null;
            }
            object ob = null;
            if (builtString.TryGetValue(ConnectionStringProperties.Provider, out ob))
            {
                if (String.IsNullOrEmpty(ob.ToString()))
                {
                    return "System.Data.SqlClient";  // This is the default for a Provider String if it isn't provided.  http://technet.microsoft.com/en-au/subscriptions/system.configuration.connectionstringsettings.providername(v=vs.90)
                }
                else
                {
                    return ob.ToString();
                }
            }
            else
            {
                return "System.Data.SqlClient";  // This is the default for a Provider String if it isn't provided.  http://technet.microsoft.com/en-au/subscriptions/system.configuration.connectionstringsettings.providername(v=vs.90)
            }
        }


        /// <summary>
        /// Try and create a connection string builder from the input string.
        /// </summary>
        /// <param name="connectionString">the connection string to parse</param>
        /// <returns>a connection string builder or NULL</returns>
        private DbConnectionStringBuilder GetConnectionStringBuilder(string connectionString)
        {
            DbConnectionStringBuilder builder = null;

            // try to create an OledbConnectionString
            try
            {
                builder = new OleDbConnectionStringBuilder(connectionString);

                // if we reach here, we were able to parse it 
            }
            catch (System.Exception)
            {
            }

            return builder;
        }

        /// <summary>
        ///  returns True if the providers in both connection strings match
        /// </summary>
        /// <param name="builder1"></param>
        /// <param name="builder2"></param>
        /// <returns></returns>
        private bool CompareProviders(DbConnectionStringBuilder builder1, DbConnectionStringBuilder builder2)
        {
            bool providersMatch = false;
            object ob = null;

            if (builder1.TryGetValue(ConnectionStringProperties.Provider, out ob))
            {
                string provider1 = ob.ToString();

                if (builder2.TryGetValue(ConnectionStringProperties.Provider, out ob))
                {
                    string provider2 = ob.ToString();
                    if (provider1.ToLower() == provider2.ToLower())
                    {
                        providersMatch = true;
                    }
                    else
                    {
                        if ((provider1.StartsWith("SQLNCLI") || (provider1.StartsWith("SQLOLEDB")))
                            && (provider2.StartsWith("SQLNCLI") || provider2.StartsWith("SQLOLEDB")))
                        {
                            providersMatch = true;
                        }
                    }
                }
            }

            return providersMatch;
        }

        /// <summary>
        /// Tries to get the server out of the connection strings by looking for common ways of specifying them.
        /// </summary>
        /// <param name="builder1"></param>
        /// <returns></returns>
        private string GetServerName(DbConnectionStringBuilder builder1)
        {
            object ob = null;
            string dataSource = null;
            builder1.TryGetValue(ConnectionStringProperties.DataSource, out ob);
            if ((ob == null) || (string.IsNullOrEmpty(ob.ToString())))
                builder1.TryGetValue(ConnectionStringProperties.Server, out ob);
            if ((ob == null) || (string.IsNullOrEmpty(ob.ToString())))
                builder1.TryGetValue(ConnectionStringProperties.Location, out ob);

            if (ob != null)
            {
                dataSource = ob.ToString();
            }

            return dataSource;
        }

        /// <summary>
        ///  compares if the servers in the two connection strings are the same.
        /// </summary>
        /// <param name="builder1"></param>
        /// <param name="builder2"></param>
        /// <returns></returns>
        private bool CompareServers(DbConnectionStringBuilder builder1, DbConnectionStringBuilder builder2)
        {
            bool matched = false;
            string dataSource1 = GetServerName(builder1);
            string dataSource2 = GetServerName(builder2);

            if (dataSource1.ToLower() == dataSource2.ToLower())
            {
                matched = true;
            }
            else
            {
                if (dataSource1 == "." || (string.Compare(dataSource1, "localhost", true) == 0) || (string.Compare(dataSource1, "(local)", true) == 0))
                {
                    if (dataSource2 == "." || (string.Compare(dataSource2, "localhost", true) == 0) || (string.Compare(dataSource2, "(local)", true) == 0))
                    {
                        matched = true;
                    }
                }
            }

            return matched;
        }

        /// <summary>
        /// Compares the names of databases within connection strings.
        /// </summary>
        /// <param name="builder1"></param>
        /// <param name="builder2"></param>
        /// <returns></returns>
        private bool CompareDatabases(DbConnectionStringBuilder builder1, DbConnectionStringBuilder builder2)
        {
            string db1 = GetDatabaseName(builder1);
            string db2 = GetDatabaseName(builder2);
            if (db1 == null)
                return false;
            if (db2 == null)
                return false;
            if (databasePrefixExclusions.Count > 0)
            {
                foreach (string dbPrefix in databasePrefixExclusions)
                {
                    if (db1.StartsWith(dbPrefix))
                    {
                        db1 = db1.Substring(dbPrefix.Length);
                    }
                    if (db2.StartsWith(dbPrefix))
                    {
                        db2 = db2.Substring(dbPrefix.Length);
                    }
                }
            }

            return (db1.ToLower() == db2.ToLower());
        }

        /// <summary>
        ///  parses the database out of connection string by understanding the different ways providers store
        ///  this information.  And then it will STRIP the prefix from it (if it exists in the exclusion list)
        /// </summary>
        /// <param name="builder"></param>
        /// <returns></returns>
        private string GetDatabaseName(DbConnectionStringBuilder builder)
        {
            object ob = null;

            builder.TryGetValue(ConnectionStringProperties.InitialCatalog, out ob);
            if ((ob == null) || (string.IsNullOrEmpty(ob.ToString())))
            {
                builder.TryGetValue(ConnectionStringProperties.Database, out ob);
            }

            string database = null;

            if (ob != null)
                database = ob.ToString();

            if (databasePrefixExclusions.Count > 0)
            {
                foreach (string dbPrefix in databasePrefixExclusions)
                {
                    if (database.StartsWith(dbPrefix))
                    {
                        database = database.Substring(dbPrefix.Length);
                        break;
                    }
                }
            }

            return database;
        }
        #endregion


        public mdSSISContainer GetSSISContainer(mdExecution execution, String name, String containerType, String description, mdSSISPackage ssisPackage)
        {
            System.Diagnostics.Debug.Assert(execution != null);
            System.Diagnostics.Debug.Assert(ssisPackage != null);

            mdSSISContainer container = (from mdSSISContainer cont in dbContext.mdSSISContainers where cont.ContainerName == name & cont.mdSSISPackages_SSISPackageID == ssisPackage.SSISPackageID select cont).FirstOrDefault();
            if (container == null)
            {
                container = dbContext.mdSSISContainers.Add(new mdSSISContainer { mdSSISPackages_SSISPackageID = ssisPackage.SSISPackageID, ContainerName = name, ContainerType = containerType, ContainerDescription = description });
                dbContext.SaveChanges();
            }
            return container;
        }
    }
}
